package com.cennavi.minemap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import com.cennavi.minemap.bean.RoleBean;
import com.cennavi.minemap.bean.UserBean;
import com.cennavi.minemap.util.HmacSHA256Utils;

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@Component
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class })
public class UserDao extends AbstractDao {

	public int insert(final UserBean userBean) {
		jdbcTemplate.setDataSource(authDataSource);
		final String sql = "INSERT INTO T_USER (username, password, mail, createTime, contact, company) " + "VALUES(?, ?, ?, ?, ?, ?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql,
						new String[] { "id" });
				ps.setString(1, userBean.getUsername());
				ps.setString(2, userBean.getPassword());
				ps.setString(3, userBean.getMail());
				ps.setString(4, userBean.getCreateTime());
				ps.setString(5, userBean.getContact());
				ps.setString(6, userBean.getCompany());
				return ps;
			}
		}, keyHolder);
		
		
		return keyHolder.getKey().intValue();
	}
	
	public int insertUserRole(final int userId) {
		jdbcTemplate.setDataSource(authDataSource);
		try{
			final String sql = "INSERT INTO T_USER_ROLE (userid, roleid) " + "VALUES(?, ?)";
			jdbcTemplate.update(sql, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setInt(1, userId);
					ps.setInt(2, 3);
				}
			});
			
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	public int update(final UserBean userBean) {
		try {
			jdbcTemplate.setDataSource(authDataSource);
			//修改用户资料不可以修改密码，以防万一
			userBean.setPassword(null);
			String setSql = super.getSetSql(userBean);	
			
			String sql = "UPDATE T_USER SET " + setSql + " WHERE id = ?";
			jdbcTemplate.update(sql, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setInt(1, userBean.getId());
				}
			});
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	public List<UserBean> queryById(int id) {
		jdbcTemplate.setDataSource(authDataSource);
		String sql = "SELECT id, username, password, type, name, mail, contact, createTime, mailStatus, "
				+ "contactStatus, company FROM T_USER WHERE id = ?";
		return jdbcTemplate.query(sql, new Object[] { id }, new UserMapper());
	}
	
	public List<UserBean> queryByMail(String mail) {
		jdbcTemplate.setDataSource(authDataSource);
		String sql = "SELECT id, username, password, type, name, mail, contact, createTime, mailStatus, "
				+ "contactStatus, company FROM T_USER WHERE mail = ?";
		return jdbcTemplate.query(sql, new Object[] { mail }, new UserMapper());
	}

	public List<UserBean> queryByUsername(String username) {
		jdbcTemplate.setDataSource(authDataSource);
		String sql = "SELECT id, username, password, type, name, mail, contact, createTime, mailStatus, "
				+ "contactStatus, company FROM T_USER WHERE username = ?";
		return jdbcTemplate.query(sql, new Object[] { username }, new UserMapper());
	}
	
//	public List<UserBean> login(String username, String password, String where) {
//		jdbcTemplate.setDataSource(pgDataSource);
//		String sql = "SELECT * FROM MM_USER where " + where + " = ? and password = ?";
//		return (List<UserBean>) jdbcTemplate.query(sql, new Object[] { username, password }, new UserMapper());
//	}
	
	public List<UserBean> login(String username, String password, String where) {
		jdbcTemplate.setDataSource(authDataSource);
		String sql = "SELECT id, username, password, type, name, mail, contact, createTime, mailStatus, "
				+ "contactStatus, company FROM T_USER where " + where + " = ? and password = ?";
		return jdbcTemplate.query(sql, new Object[] { username, password }, new UserMapper());
	}


	public int uniqueVerify(String key, String value) {
		jdbcTemplate.setDataSource(authDataSource);
		String sql = "SELECT count(*) FROM T_USER where " + key + " = ? ";
		return jdbcTemplate.queryForObject(sql, new Object[] { value }, Integer.class);
	}
	
	public List<RoleBean> queryRoleByUserId(Integer userId) {
		jdbcTemplate.setDataSource(authDataSource);
		String sql = "SELECT r.id, r.code, r.name, r.type FROM t_role r, t_user_role ur WHERE r.id = ur.roleid AND ur.userid = ?";
		return jdbcTemplate.query(sql, new Object[] { userId }, new RoleMapper());
	}

	protected class UserMapper implements RowMapper<UserBean> {
		public UserBean mapRow(ResultSet rs, int rowNum) throws SQLException {
			UserBean user = new UserBean();
			user.setId(rs.getInt("id"));
			user.setUsername(rs.getString("username"));
			user.setType(rs.getInt("type"));
			user.setName(rs.getString("name"));
			user.setMail(rs.getString("mail"));
			user.setContact(rs.getString("contact"));
			user.setCreateTime(rs.getString("createTime"));
			user.setMailStatus(rs.getShort("mailStatus"));
			user.setContactStatus(rs.getShort("contactStatus"));
			user.setCompany(rs.getString("company"));
			user.setDigest(HmacSHA256Utils.digest(rs.getString("username"), rs.getString("password")));
			return user;
		}
	}
	
	protected class RoleMapper implements RowMapper<RoleBean> {
		public RoleBean mapRow(ResultSet rs, int rowNum) throws SQLException {
			RoleBean role = new RoleBean();
			role.setId(rs.getInt("id"));
			role.setCode(rs.getString("code"));
			role.setName(rs.getString("name"));
			role.setType(rs.getShort("type"));
			return role;
		}
	}
	
	public int updateVerification(final String mail, final String vCode, final String vDate) {
		try {
			jdbcTemplate.setDataSource(authDataSource);
			String sql = "UPDATE T_USER SET vcode=?, vtime=? WHERE mail = ?";
			jdbcTemplate.update(sql, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, vCode);
					ps.setString(2, vDate);
					ps.setString(3, mail);
				}
			});
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	public List<UserBean> queryByVCode(String mail, String vCode, String now) {
		jdbcTemplate.setDataSource(authDataSource);
		String sql = "SELECT id, username, password, type, name, mail, contact, createTime, mailStatus, "
				+ "contactStatus, company FROM T_USER WHERE mail = ? AND vcode = ? AND vtime >= ?";
		return jdbcTemplate.query(sql, new Object[] { mail, vCode, now}, new UserMapper());
	}
	
	public int updatePassword(final UserBean userBean) {
		try {
			jdbcTemplate.setDataSource(authDataSource);
			String sql = "UPDATE T_USER SET vcode = null, vtime = null, password = ? WHERE id = ?";
			jdbcTemplate.update(sql, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, userBean.getPassword());
					ps.setInt(2, userBean.getId());
				}
			});
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	public int mailboxVeri(final UserBean userBean) {
		try {
			jdbcTemplate.setDataSource(authDataSource);
			String sql = "UPDATE T_USER SET vcode = null, vtime = null, mailStatus = 1 WHERE id = ?";
			jdbcTemplate.update(sql, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setInt(1, userBean.getId());
				}
			});
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}
}
